package com.zhw.controller;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.handler.inter.IExcelDataHandler;
import com.zhw.ExcelHandler.UserExcelHandler;
import com.zhw.entity.Person;
import com.zhw.entity.User;
import com.zhw.util.EasyPoiUtils;
import com.zhw.util.FileUtil;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @auther weizhang52 on 2018/11/21
 * @description
 * @modify
 */
@Controller
@RequestMapping("/excel")
public class ExcelImportController {

    private static final Logger log = LoggerFactory.getLogger(ExcelImportController.class);

    @PostMapping("/excelImport.do")
    @ResponseBody
    public void excelImport(@RequestParam("file") MultipartFile file) {
        ImportParams importParams = new ImportParams();
        // 数据处理
        IExcelDataHandler<User> handler = new UserExcelHandler();
        handler.setNeedHandlerFields(new String[] { "姓名","年龄" });// 注意这里对应的是excel的列名。也就是对象上指定的列名。
        importParams.setDataHandler(handler);
        importParams.setHeadRows(0);
//        importParams.setLastOfInvalidRow(2);
        importParams.getImportFields();
        System.out.println(importParams.getImportFields());

        // 需要验证
        importParams.setNeedVerfiy(true);
        System.out.println("importParams.getLastOfInvalidRow():"+importParams.getLastOfInvalidRow());
        String[] headers=importParams.getImportFields();
//        System.out.println("importParams.getImportFields():"+headers.length+headers[0]);

        try {
            ExcelImportResult<User> result = ExcelImportUtil.importExcelMore(file.getInputStream(), User.class,
                    importParams);
            System.out.println("importParams.getLastOfInvalidRow():"+importParams.getLastOfInvalidRow());
            log.info("解析excel数据：",result);
            List<User> successList = result.getList();
            List<User> failList = result.getFailList();
            log.info("解析成功的excel数据：",successList.get(0).getName());
            System.out.println(successList);
            log.info("是否存在验证未通过的数据:" + result.isVerfiyFail());
            log.info("验证通过的数量:" + successList.size());
            log.info("验证未通过的数量:" + failList.size());

            for (User user : successList) {
                log.info("成功列表信息:ID=" + user.getId() + user.getName() + "-"
                        + new SimpleDateFormat("yyyy-MM-dd").format(user.getBirthday()));
            }
            for (User user : failList) {
                log.info("失败列表信息:" + user.getName());
            }
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
    }

    @RequestMapping("/exportPerson")
    public void export(HttpServletResponse response) throws IOException {

        log.info("入参:");
        //模拟从数据库获取需要导出的数据
        List<Person> personList = new ArrayList<>();
        Person person1 = new Person("路飞","1",new Date());
        Person person2 = new Person("娜美","2", new Date());
        Person person3 = new Person("索隆","1", new Date());
        Person person4 = new Person("小狸猫","1", new Date());
        personList.add(person1);
        personList.add(person2);
        personList.add(person3);
        personList.add(person4);

        //导出操作   下载
        Workbook workbook=FileUtil.exportExcel(personList,"花名册","草帽一伙",Person.class,"海贼王.xls",response);
        //存本地
        File savefile = new File("D:/excel/");
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream("D:/excel/SKU.xlsx");
            try {
                workbook.write(fos);
            } catch (IOException e) {
                e.printStackTrace();
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }finally {
            fos.close();
        }


    }

    @RequestMapping("/importPerson")
    public void importExcel(){
        String filePath = "D:/excel/海贼王.xls";
        //解析excel，
        List<Person> personList = FileUtil.importExcel(filePath,0,1,Person.class);
        //也可以使用MultipartFile,使用 FileUtil.importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass)导入
        System.out.println("导入数据一共【"+personList.size()+"】行");

        //TODO 保存数据库
    }

    @GetMapping("/exportDemo")
    public void importDemo(HttpServletResponse response){

        long timestamp = System.currentTimeMillis();
        // excel标题
        String excelTitle = "title";
        // excel文件名称:文件名+时间戳+格式后缀
        String excelName = "name" + timestamp+".xls";

        List<Person> personList = new ArrayList<>();
        Person person1 = new Person("路飞","1",new Date());
        Person person2 = new Person("娜美","2", new Date());
        Person person3 = new Person("索隆","1", new Date());
        Person person4 = new Person("小狸猫","1", new Date());
        personList.add(person1);
        personList.add(person2);
        personList.add(person3);
        personList.add(person4);

        // 导出:自定义导出属性
        EasyPoiUtils.exportExcel(personList, excelTitle, "sheetName",
                Person.class, excelName, response);

    }

}
